from datetime import datetime

from flask import Blueprint,request
from sqlalchemy import func,text,and_,or_
from datetime import datetime
from applications.extensions import db
from applications.models import *
from applications.common.utils.rights import authorize
from applications.common.utils.validate import str_escape

bp = Blueprint('store', __name__, url_prefix='/store')

# 获取原料入库批号
@bp.post('/PHYPyl')
@authorize("store:raw:add:yp", log=False)
def get_newPH_raw_yp():
    '''
    currentdate 日期
    rawid       原料档案id
    '''
    req_json = request.get_json(force=True)
    current_date = str_escape(req_json.get('current_date'))
    current_date=datetime.strptime(current_date,'%Y-%m-%d').strftime('%y%m')
    id_ = str_escape(req_json.get('id'))
    result={'success':True,'msg':'成功'}
    item=StoreRawYPModel.query.join(RawYPModel).filter(
        func.date_format(StoreRawYPModel.date_time, '%y%m') == current_date,
        RawYPModel.id==id_,StoreRawYPModel.type.in_(['入库单','其他入库单'])
    ).order_by(StoreRawYPModel.批号.desc()).first()
    if item :
        result['批号']=str(item.批号[:-3])+str(int(item.批号[-3:])+1).zfill(3)
    else:
        raw=RawYPModel.query.filter_by(id=id_).first()
        if raw:
            result['批号'] = str(raw.代码) +'-'+ current_date+'001'
        else:
            result['批号'] = '-'+ current_date+'001'
            result['success'] = False
            result['msg'] = '未查到产品数据'
    print(result)
    return result
# 获取原料小样请验批号
@bp.post('/PHYPylSample')
@authorize("store:raw:verify:add:yp", log=False)
def get_newPH_raw_yp_sample():
    '''
    daima     代码
    current_date  当前时间
    '''
    req_json = request.get_json(force=True)
    current_date = str_escape(req_json.get('current_date'))
    current_date=datetime.strptime(current_date,'%Y-%m-%d').strftime('%y%m')
    daima = str_escape(req_json.get('daima'))
    result={'success':True,'msg':'成功'}
    item=VerifyRawYPModelSample.query.join(RawYPModel).filter(
        func.date_format(VerifyRawYPModelSample.date_time, '%y%m') == current_date,
        RawYPModel.代码==daima
    ).order_by(VerifyRawYPModelSample.批号.desc()).first()
    if item :
        result['批号']=str(item.批号[:-3])+str(int(item.批号[-3:])+1).zfill(3)
    else:

        result['批号'] = f'XY{daima}-{current_date}001'

    print('pihao',result)
    return result

# 获取货位
@bp.get('/HVYPyl')
@authorize("store:raw:add:yp", log=False)
def get_hv_raw_yp():
    '''
    currentdate 日期
    rawid       原料档案id
    '''
    sql=text(f"""SELECT * from 货位管理_yp_原料 where 
        (parent_id=0 and `enable`=1) or 
        (parent_id !=0 and parent_id in 
        (select id from `货位管理_yp_原料` where  `enable`=1))""")
    results=db.session.execute(sql).fetchall()
    items=[]
    for resu in results:
        dic={}
        for filed,value in zip(resu._fields,resu):
            dic[filed]=value

        dic['hv_text']='->'.join(get_hv_text(dic,results,[]))
        items.append(dic)
    result={'success':True,'msg':'成功'}
    result['data']=items
    return result
# 获取货位
@bp.post('/HVYPyl')
@authorize("store:raw:edit:yp", log=False)
def post_hv_raw_yp():
    '''
    currentdate 日期
    rawid       原料档案id
    '''
    req_json = request.get_json(force=True)
    id = req_json.get("id")
    one = LocationRawYP.query.filter_by(id=id).first()
    sql=text(f"""SELECT * from 货位管理_yp_原料 where 
        (parent_id=0 and `enable`=1) or 
        (parent_id !=0 and parent_id in 
        (select id from `货位管理_yp_原料` where  `enable`=1))""")
    results=db.session.execute(sql).fetchall()
    items=[]
    is_in=False
    for resu in results:

        dic={}
        for filed,value in zip(resu._fields,resu):
            dic[filed]=value
        if one.to_dict()==dic:
            is_in=True

        dic['hv_text']='->'.join(get_hv_text(dic,results,[]))
        items.append(dic)
    if not is_in:
        dic = one.to_dict()
        dic['hv_text'] = '->'.join(get_hv_text(dic, results, []))
        items.append(dic)
    result={'success':True,'msg':'成功'}
    result['data']=items
    return result

def get_hv_text(dic,results,hv_text=[]):
    if dic['type'] == '0':
        hv_text.append(dic['name'])
        return hv_text
    elif dic['type'] == '1':
        for result in results:
            if dic['parent_id']==result.id:
                hv_text.append(dic['name'])
                dic2 = {}
                for filed, value in zip(result._fields, result):
                    dic2[filed] = value
                return get_hv_text(dic2,results,hv_text=hv_text)

    elif dic['type']=='2':
        for result in results:
            if dic['parent_id']==result.id:
                hv_text.append(dic['name'])
                dic2 = {}
                for filed, value in zip(result._fields, result):
                    dic2[filed] = value
                return  get_hv_text(dic2,results,hv_text=hv_text)


# 获取库管员、QA、采购员
@bp.get('/userYPyl')
@authorize("store:raw:add:yp", log=False)
def get_user_raw_yp():
    '''
    currentdate 日期
    rawid       原料档案id
    '''
    result={}
    sql=text(f"""select id,realname from admin_user where id in (
        select user_id from admin_user_role where role_id in (
        select role_id from admin_role_power where power_id in 
        (select id from admin_power where code='user:power:raw:yp:keeper'))) and enable=1""")
    results_keeper=db.session.execute(sql).fetchall()
    items=[]
    for resu in results_keeper:
        dic={}
        for filed,value in zip(resu._fields,resu):
            dic[filed]=value
        items.append(dic)
    result['keeper'] = items
    sql = text(f"""select id,realname from admin_user where id in (
            select user_id from admin_user_role where role_id in (
            select role_id from admin_role_power where power_id in 
            (select id from admin_power where code='user:power:raw:yp:qa'))) and enable=1""")
    results_qa = db.session.execute(sql).fetchall()
    items = []
    for resu in results_qa:
        dic = {}
        for filed, value in zip(resu._fields, resu):
            dic[filed] = value
        items.append(dic)
    result['qa'] = items

    sql = text(f"""select id,realname from admin_user where id in (
                select user_id from admin_user_role where role_id in (
                select role_id from admin_role_power where power_id in 
                (select id from admin_power where code='user:power:raw:yp:buyer'))) and enable=1""")
    results_qa = db.session.execute(sql).fetchall()
    items = []
    for resu in results_qa:
        dic = {}
        for filed, value in zip(resu._fields, resu):
            dic[filed] = value
        items.append(dic)
    result['buyer'] = items

    sql = text(f"""select id,realname from admin_user where id in (
                    select user_id from admin_user_role where role_id in (
                    select role_id from admin_role_power where power_id in 
                    (select id from admin_power where code='user:power:raw:yp:sampler'))) and enable=1""")
    results_qa = db.session.execute(sql).fetchall()
    items = []
    for resu in results_qa:
        dic = {}
        for filed, value in zip(resu._fields, resu):
            dic[filed] = value
        items.append(dic)
    result['sampler'] = items

    sql = text(f"""select id,realname from admin_user where id in (
                        select user_id from admin_user_role where role_id in (
                        select role_id from admin_role_power where power_id in 
                        (select id from admin_power where code='user:power:raw:yp:tester'))) and enable=1""")
    results_qa = db.session.execute(sql).fetchall()
    items = []
    for resu in results_qa:
        dic = {}
        for filed, value in zip(resu._fields, resu):
            dic[filed] = value
        items.append(dic)
    result['tester'] = items

    sql = text(f"""select id,realname from admin_user where id in (
                            select user_id from admin_user_role where role_id in (
                            select role_id from admin_role_power where power_id in 
                            (select id from admin_power where code='user:power:raw:yp:releaser'))) and enable=1""")
    results_qa = db.session.execute(sql).fetchall()
    items = []
    for resu in results_qa:
        dic = {}
        for filed, value in zip(resu._fields, resu):
            dic[filed] = value
        items.append(dic)
    result['releaser'] = items

    result['success'] =True
    result['msg'] = '成功'
    return result